﻿using System;
using System.Data;
using System.Transactions;
using System.Data.Common;
using System.Data.SqlClient;
using System.Collections.Generic;
using System.Linq;
using System.Text;

using Skyworth.CRM.Service.Entity;
using Skyworth.CRM.Service.IDAL;
using Microsoft.Practices.EnterpriseLibrary.Data;

namespace Skyworth.CRM.DAL
{
    public class StockDAL : SuperDataBase, IStockDAL
    {
        public void Add(Stock model)
        {
            try
            {
                StringBuilder strSql = new StringBuilder();
                strSql.Append("insert into T_BasicData_Stock(");
                strSql.Append("FNumber,FName,FType,FDeptId,FStatus,FDree,FFrelation,FSrelation,FTel,FMobil)");
                strSql.Append(" values (");
                strSql.Append("@FNumber,@FName,@FType,@FDeptId,@FStatus,@FDree,@FFrelation,@FSrelation,@FTel,@FMobil)");
                //strSql.Append(";select @@IDENTITY");
                DbCommand cmd = DB.GetSqlStringCommand(strSql.ToString());
                DB.AddInParameter(cmd, "@FNumber", DbType.String, model.FNumber);
                DB.AddInParameter(cmd, "@FName", DbType.String, model.FName);
                DB.AddInParameter(cmd, "@FType", DbType.Int32, model.FType);
                DB.AddInParameter(cmd, "@FDeptId", DbType.Int32, model.FDeptId);
                DB.AddInParameter(cmd, "@FStatus", DbType.Int32, model.FStatus);
                DB.AddInParameter(cmd, "@FDree", DbType.String, model.FDree);
                DB.AddInParameter(cmd, "@FFrelation", DbType.String, model.FFrelation);
                DB.AddInParameter(cmd, "@FSrelation", DbType.String, model.FSrelation);
                DB.AddInParameter(cmd, "@FTel", DbType.String, model.FTel);
                DB.AddInParameter(cmd, "@FMobil", DbType.String, model.FMobil);
                DB.ExecuteNonQuery(cmd);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public void Update(Stock model)
        {
            try
            {
                StringBuilder strSql = new StringBuilder();
                strSql.Append("update T_BasicData_Stock set ");
                strSql.Append("FNumber=@FNumber,");
                strSql.Append("FName=@FName,");
                strSql.Append("FType=@FType,");
                strSql.Append("FDeptId=@FDeptId,");
                strSql.Append("FStatus=@FStatus,");
                strSql.Append("FDree=@FDree,");
                strSql.Append("FFrelation=@FFrelation,");
                strSql.Append("FSrelation=@FSrelation,");
                strSql.Append("FTel=@FTel,");
                strSql.Append("FMobil=@FMobil");
                strSql.Append(" where FitemId=@FitemId");
                DbCommand cmd = DB.GetSqlStringCommand(strSql.ToString());
                DB.AddInParameter(cmd, "@FitemId", DbType.Int32, model.FitemId);
                DB.AddInParameter(cmd, "@FNumber", DbType.String, model.FNumber);
                DB.AddInParameter(cmd, "@FName", DbType.String, model.FName);
                DB.AddInParameter(cmd, "@FType", DbType.Int32, model.FType);
                DB.AddInParameter(cmd, "@FDeptId", DbType.Int32, model.FDeptId);
                DB.AddInParameter(cmd, "@FStatus", DbType.Int32, model.FStatus);
                DB.AddInParameter(cmd, "@FDree", DbType.String, model.FDree);
                DB.AddInParameter(cmd, "@FFrelation", DbType.String, model.FFrelation);
                DB.AddInParameter(cmd, "@FSrelation", DbType.String, model.FSrelation);
                DB.AddInParameter(cmd, "@FTel", DbType.String, model.FTel);
                DB.AddInParameter(cmd, "@FMobil", DbType.String, model.FMobil);
                DB.ExecuteNonQuery(cmd);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public void Delete(int FItemId)
        {
            try
            {
                StringBuilder strSql = new StringBuilder();
                strSql.Append("delete from T_BasicData_Stock ");
                strSql.Append(" where FitemId=@FitemId");
                DbCommand cmd = DB.GetSqlStringCommand(strSql.ToString());
                DB.AddInParameter(cmd, "@FitemId", DbType.Int32, FItemId);
                DB.ExecuteNonQuery(cmd);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public Stock GetModel(int fItemId)
        {
            try
            {
                Stock model = null;
                StringBuilder strSql = new StringBuilder();
                strSql.Append("select  top 1 FitemId,FNumber,FName,FType,FDeptId,FStatus,FDree,FFrelation,FSrelation,FTel,FMobil from T_BasicData_Stock ");
                strSql.Append(" where FitemId=@FitemId");

                DbCommand cmd = DB.GetSqlStringCommand(strSql.ToString());
                DB.AddInParameter(cmd, "@FitemId", DbType.Int32, fItemId);
                using (IDataReader dr = DB.ExecuteReader(cmd))
                {
                    while (dr.Read())
                    {
                        model = new Stock();
                        if (dr["FitemId"].ToString() != "")
                        {
                            model.FitemId = int.Parse(dr["FitemId"].ToString());
                        }
                        model.FNumber = dr["FNumber"].ToString();
                        model.FName = dr["FName"].ToString();
                        if (dr["FType"].ToString() != "")
                        {
                            model.FType = int.Parse(dr["FType"].ToString());
                        }
                        if (dr["FDeptId"].ToString() != "")
                        {
                            model.FDeptId = int.Parse(dr["FDeptId"].ToString());
                        }
                        if (dr["FStatus"].ToString() != "")
                        {
                            model.FStatus = int.Parse(dr["FStatus"].ToString());
                        }
                        model.FDree = dr["FDree"].ToString();
                        model.FFrelation = dr["FFrelation"].ToString();
                        model.FSrelation = dr["FSrelation"].ToString();
                        model.FTel = dr["FTel"].ToString();
                        model.FMobil = dr["FMobil"].ToString();
                    }
                }
                return model;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public DataTable Query(string strWhere)
        {
            try
            {
                StringBuilder strSql = new StringBuilder();
                strSql.Append(@"select * from
(select tbs.FitemId,tbs.FNumber,tbs.FName,tbs.FType,'经销商' as TypeName,tbs.FDeptId,tbc.FName as DepartmentName,
tbs.FStatus,tbs.FDree,tbs.FFrelation,tbs.FSrelation,tbs.FTel,tbs.FMobil
FROM T_BasicData_Stock as tbs left join T_BasicData_Customer as tbc on tbs.FDeptId=tbc.FItemId
where FType=1
union
select tbs2.FitemId,tbs2.FNumber,tbs2.FName,tbs2.FType,
case tbs2.FType when 2 then '分公司' else '总部' end as TypeName,tbs2.FDeptId,tbd.DepartmentName,
tbs2.FStatus,tbs2.FDree,tbs2.FFrelation,tbs2.FSrelation,tbs2.FTel,tbs2.FMobil
FROM T_BasicData_Stock as tbs2 left join T_BasicData_Department as tbd on tbs2.FDeptId=tbd.DepartmentId
where FType<>1) as vst where 1=1");
                if (strWhere.Trim() != "")
                {
                    strSql.Append(strWhere);
                }
                DataSet ds = DB.ExecuteDataSet(CommandType.Text, strSql.ToString());
                return ds.Tables[0];
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
    }
}